/************************************************************************************/
/*  Program name CAPAPIMP_FIN_OPT.sql                                               */
/*  Creater:  WONG Wing Kin (PCCW - UniHub)                                         */
/*************   History   *************************                                */
/* 30 NOV, 2005   WONG Wing Kin         First created                               */
/************************************************************************************/
SET SERVEROUTPUT ON SIZE 1000000;

DECLARE
	lv_Prog_ID		VARCHAR2(100)	:=	'CAPAPIMP_FIN_OPT';

	lv_Scc_Count	NUMBER			:=	0;
	lv_Err_Count	NUMBER			:=	0;
	lv_Err_Msg		VARCHAR2(500)	:=	'No Error';

	CURSOR Cur_F IS
	SELECT
		*
	FROM
		CRST_AP_FINANCIALS_PARAM;

	lv_COA_ID			NUMBER;

BEGIN
	FOR C1 IN Cur_F LOOP
		BEGIN

			lv_COA_ID	:=	CAPBRIMP_COMMON.GetCOAID(C1.SOB_SHORT_NAME);

			INSERT INTO FINANCIALS_SYSTEM_PARAMS_ALL
			VALUES
			(
			C1.LAST_UPDATE_DATE,
			CAPBRIMP_COMMON.GetUser(C1.LAST_UPDATED_BY_NAME),		--LAST_UPDATED_BY,
			CAPBRIMP_COMMON.GetSOBID(C1.SOB_SHORT_NAME),			--SET_OF_BOOKS_ID,
			C1.PAYMENT_METHOD_LOOKUP_CODE,
			C1.USER_DEFINED_VENDOR_NUM_CODE,
			C1.VENDOR_NUM_START_NUM,
			CAPBRIMP_COMMON.GetLocation(C1.SHIP_TO_LOC_NAME),		--SHIP_TO_LOCATION_ID,
			CAPBRIMP_COMMON.GetLocation(C1.BILL_TO_LOC_NAME),		--BILL_TO_LOCATION_ID,
			C1.SHIP_VIA_LOOKUP_CODE,
			C1.FOB_LOOKUP_CODE,
			CAPBRIMP_COMMON.GetAPTerm(C1.AP_TERM_NAME),				--TERMS_ID,
			C1.ALWAYS_TAKE_DISC_FLAG,
			C1.PAY_DATE_BASIS_LOOKUP_CODE,
			C1.INVOICE_CURRENCY_CODE,
			C1.PAYMENT_CURRENCY_CODE,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.ACCTS_PAY_CCID_D),		--ACCTS_PAY_CODE_COMBINATION_ID,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.PREPAY_CCID_D),			--PREPAY_CODE_COMBINATION_ID,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.DISC_TAKEN_CCID_D),		--DISC_TAKEN_CODE_COMBINATION_ID,
			C1.FUTURE_PERIOD_LIMIT,
			C1.RESERVE_AT_COMPLETION_FLAG,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.RES_ENCUMB_CCID_D),		--RES_ENCUMB_CODE_COMBINATION_ID,
			C1.REQ_ENCUMBRANCE_FLAG,
			C1.REQ_ENCUMBRANCE_TYPE_ID,
			C1.PURCH_ENCUMBRANCE_FLAG,
			C1.PURCH_ENCUMBRANCE_TYPE_ID,
			C1.INV_ENCUMBRANCE_TYPE_ID,
			C1.MANUAL_VENDOR_NUM_TYPE,
			CAPBRIMP_COMMON.GetOrgID(C1.INVENTORY_ORG_NAME),		--INVENTORY_ORGANIZATION_ID,
			C1.LAST_UPDATE_LOGIN,
			C1.CREATION_DATE,
			CAPBRIMP_COMMON.GetUser(C1.CREATED_BY_NAME),			--CREATED_BY,
			C1.FREIGHT_TERMS_LOOKUP_CODE,
			C1.RFQ_ONLY_SITE_FLAG,
			C1.RECEIPT_ACCEPTANCE_DAYS,
			CAPBRIMP_COMMON.GetBGID(C1.BG_NAME),					--BUSINESS_GROUP_ID,
			C1.EXPENSE_CHECK_ADDRESS_FLAG,
			C1.TERMS_DATE_BASIS,
			C1.USE_POSITIONS_FLAG,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.RATE_VAR_CCID_D),		--RATE_VAR_CODE_COMBINATION_ID,
			C1.HOLD_UNMATCHED_INVOICES_FLAG,
			C1.EXCLUSIVE_PAYMENT_FLAG,
			C1.REVISION_SORT_ORDERING,
			C1.VAT_REGISTRATION_NUM,
			C1.VAT_COUNTRY_CODE,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.RATE_VAR_GAIN_CCID_D),	--RATE_VAR_GAIN_CCID,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.RATE_VAR_LOSS_CCID_D),	--RATE_VAR_LOSS_CCID,
			CAPBRIMP_COMMON.GetOrgID(C1.ORG_NAME),					--ORG_ID,
			C1.BANK_CHARGE_BEARER,
			C1.VAT_CODE,
			C1.MATCH_OPTION,
			C1.NON_RECOVERABLE_TAX_FLAG,
			C1.TAX_ROUNDING_RULE,
			C1.PRECISION,
			C1.MINIMUM_ACCOUNTABLE_UNIT,
			C1.DEFAULT_RECOVERY_RATE,
			C1.CASH_BASIS_ENC_NR_TAX,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.FUTURE_DATED_PAYMENT_CCID_D),	--FUTURE_DATED_PAYMENT_CCID,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.EXPENSE_CLEARING_CCID_D),		--EXPENSE_CLEARING_CCID,
			CAPBRIMP_COMMON.GetSegment(lv_COA_ID,C1.MISC_CHARGE_CCID_D),			--MISC_CHARGE_CCID,
			C1.GLOBAL_ATTRIBUTE_CATEGORY,
			C1.GLOBAL_ATTRIBUTE1,
			C1.GLOBAL_ATTRIBUTE2,
			C1.GLOBAL_ATTRIBUTE3,
			C1.GLOBAL_ATTRIBUTE4,
			C1.GLOBAL_ATTRIBUTE5,
			C1.GLOBAL_ATTRIBUTE6,
			C1.GLOBAL_ATTRIBUTE7,
			C1.GLOBAL_ATTRIBUTE8,
			C1.GLOBAL_ATTRIBUTE9,
			C1.GLOBAL_ATTRIBUTE10,
			C1.GLOBAL_ATTRIBUTE11,
			C1.GLOBAL_ATTRIBUTE12,
			C1.GLOBAL_ATTRIBUTE13,
			C1.GLOBAL_ATTRIBUTE14,
			C1.GLOBAL_ATTRIBUTE15,
			C1.GLOBAL_ATTRIBUTE16,
			C1.GLOBAL_ATTRIBUTE17,
			C1.GLOBAL_ATTRIBUTE18,
			C1.GLOBAL_ATTRIBUTE19,
			C1.GLOBAL_ATTRIBUTE20
			);
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.Put_Line(SQLERRM);
				lv_Err_Count	:=	lv_Err_Count + 1;
		END;
		lv_Scc_Count	:=	lv_Scc_Count	+	1;
	END LOOP;

	IF	lv_Err_Count	>	0	THEN
		ROLLBACK;
	END IF;

	INSERT INTO CAPCNV_LOG(
		PROG_ID,
		PROCESS_DATE,
		RECORD_PROCESSED,
		RECORD_REJECTED,
		RECORD_EXTRACTED,
		ERR_MSG
	) VALUES (
		lv_Prog_ID,
		SYSDATE,
		lv_Scc_Count,
		lv_Err_Count,
		lv_Scc_Count-lv_Err_Count,
		lv_Err_Msg
	);

	COMMIT;
END;
/
